-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: May 01, 2026 at 05:36 AM
-- Server version: 8.2.0
-- PHP Version: 8.3.0

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `greenleaf_organics`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_DeleteProduct` (IN `p_productID` INT, OUT `p_message` VARCHAR(255))   BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_message = 'ERROR: Delete failed. Transaction rolled back.';
    END;

    START TRANSACTION;

    IF NOT EXISTS (SELECT 1 FROM Product WHERE ProductID = p_productID) THEN
        SET p_message = 'ERROR: ProductID not found.';
        ROLLBACK;
    ELSE
        DELETE FROM Product WHERE ProductID = p_productID;
        SET p_message = CONCAT('SUCCESS: Product ', p_productID, ' deleted.');
        COMMIT;
    END IF;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_InsertProduct` (IN `p_name` VARCHAR(150), IN `p_desc` TEXT, IN `p_price` DECIMAL(10,2), IN `p_stock` INT, IN `p_expiry` DATE, IN `p_supplierID` INT, OUT `p_newID` INT, OUT `p_message` VARCHAR(255))   BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_message = 'ERROR: Insert failed. Transaction rolled back.';
        SET p_newID = -1;
    END;

    START TRANSACTION;

    -- Validate supplier exists
    IF NOT EXISTS (SELECT 1 FROM Supplier WHERE SupplierID = p_supplierID) THEN
        SET p_message = 'ERROR: SupplierID does not exist.';
        SET p_newID = -1;
        ROLLBACK;
    ELSE
        INSERT INTO Product (ProductName, Description, Price, StockQty, ExpiryDate, SupplierID)
        VALUES (p_name, p_desc, p_price, p_stock, p_expiry, p_supplierID);

        SET p_newID = LAST_INSERT_ID();
        SET p_message = CONCAT('SUCCESS: Product inserted with ID ', p_newID);
        COMMIT;
    END IF;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_PlaceOrder` (IN `p_customerID` INT, IN `p_productID` INT, IN `p_quantity` INT, OUT `p_orderID` INT, OUT `p_message` VARCHAR(255))   BEGIN
    DECLARE v_price     DECIMAL(10,2);
    DECLARE v_stock     INT;
    DECLARE v_newOrderID INT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_orderID = -1;
        SET p_message = 'ERROR: Order placement failed. All changes rolled back to maintain data integrity.';
    END;

    START TRANSACTION;

    -- Step 1: Check customer exists
    IF NOT EXISTS (SELECT 1 FROM Customer WHERE CustomerID = p_customerID) THEN
        SET p_message = 'ROLLBACK REASON: Customer does not exist.';
        SET p_orderID = -1;
        ROLLBACK;
    ELSE

    -- Step 2: Check product exists and get price/stock
    SELECT Price, StockQty INTO v_price, v_stock
    FROM Product WHERE ProductID = p_productID;

    IF v_price IS NULL THEN
        SET p_message = 'ROLLBACK REASON: Product does not exist.';
        SET p_orderID = -1;
        ROLLBACK;
    ELSEIF v_stock < p_quantity THEN
        -- Step 3: Check sufficient stock - ROLLBACK if not enough
        SET p_message = CONCAT('ROLLBACK REASON: Insufficient stock. Requested: ',
                               p_quantity, ', Available: ', v_stock);
        SET p_orderID = -1;
        ROLLBACK;
    ELSE
        -- Step 4: Create the order header
        INSERT INTO Orders (CustomerID, OrderStatus)
        VALUES (p_customerID, 'Pending');
        SET v_newOrderID = LAST_INSERT_ID();

        -- Step 5: Insert the order item
        INSERT INTO OrderItem (OrderID, ProductID, Quantity, UnitPrice)
        VALUES (v_newOrderID, p_productID, p_quantity, v_price);

        -- Step 6: Deduct stock
        UPDATE Product
        SET StockQty = StockQty - p_quantity
        WHERE ProductID = p_productID;

        -- All steps succeeded - COMMIT
        COMMIT;
        SET p_orderID = v_newOrderID;
        SET p_message = CONCAT('SUCCESS: Order ', v_newOrderID, ' placed and committed. Stock updated.');
    END IF;
    END IF;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_UpdateProduct` (IN `p_productID` INT, IN `p_name` VARCHAR(150), IN `p_price` DECIMAL(10,2), IN `p_stock` INT, OUT `p_message` VARCHAR(255))   BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_message = 'ERROR: Update failed. Transaction rolled back.';
    END;

    START TRANSACTION;

    IF NOT EXISTS (SELECT 1 FROM Product WHERE ProductID = p_productID) THEN
        SET p_message = 'ERROR: ProductID not found.';
        ROLLBACK;
    ELSE
        UPDATE Product
        SET ProductName = p_name,
            Price       = p_price,
            StockQty    = p_stock
        WHERE ProductID = p_productID;

        SET p_message = CONCAT('SUCCESS: Product ', p_productID, ' updated.');
        COMMIT;
    END IF;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `auditlog`
--

CREATE TABLE `auditlog` (
  `LogID` int NOT NULL,
  `TableName` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `RecordID` int NOT NULL,
  `Action` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `OldValue` text COLLATE utf8mb4_unicode_ci,
  `NewValue` text COLLATE utf8mb4_unicode_ci,
  `ChangedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `auditlog`
--

INSERT INTO `auditlog` (`LogID`, `TableName`, `RecordID`, `Action`, `OldValue`, `NewValue`, `ChangedAt`) VALUES
(1, 'Product', 1, 'STOCK_UPDATE', 'StockQty=200', 'StockQty=180', '2026-05-01 03:57:10'),
(2, 'Product', 1, 'PRICE_UPDATE', 'Price=1.99', 'Price=2.29', '2026-05-01 03:57:10'),
(3, 'Orders', 9, 'INSERT', NULL, 'CustomerID=2, Status=Pending', '2026-05-01 03:57:10'),
(4, 'Product', 4, 'STOCK_UPDATE', 'StockQty=120', 'StockQty=118', '2026-05-01 03:57:10'),
(5, 'Product', 2, 'STOCK_UPDATE', 'StockQty=150', 'StockQty=140', '2026-05-01 03:57:10'),
(6, 'Product', 2, 'PRICE_UPDATE', 'Price=2.49', 'Price=2.69', '2026-05-01 03:57:10'),
(7, 'Orders', 10, 'INSERT', NULL, 'CustomerID=2, Status=Pending', '2026-05-01 04:23:44'),
(8, 'Product', 4, 'STOCK_UPDATE', 'StockQty=118', 'StockQty=116', '2026-05-01 04:23:44');

-- --------------------------------------------------------

--
-- Table structure for table `category`
--

CREATE TABLE `category` (
  `CategoryID` int NOT NULL,
  `CategoryName` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`CategoryID`, `CategoryName`) VALUES
(4, 'Eco Household'),
(3, 'Natural Skincare'),
(5, 'Organic Dairy'),
(2, 'Organic Fruit'),
(1, 'Organic Vegetables'),
(6, 'Supplements & Wellness');

-- --------------------------------------------------------

--
-- Table structure for table `customer`
--

CREATE TABLE `customer` (
  `CustomerID` int NOT NULL,
  `FirstName` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `LastName` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Email` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `RegistrationDate` date NOT NULL DEFAULT (curdate())
) ;

--
-- Dumping data for table `customer`
--

INSERT INTO `customer` (`CustomerID`, `FirstName`, `LastName`, `Email`, `Phone`, `RegistrationDate`) VALUES
(1, 'James', 'Okafor', 'james.okafor@email.com', '07700 100001', '2024-01-10'),
(2, 'Priya', 'Sharma', 'priya.sharma@email.com', '07700 100002', '2024-02-14'),
(3, 'Callum', 'Hughes', 'callum.hughes@email.com', '07700 100003', '2024-03-01'),
(4, 'Sophie', 'Tremblay', 'sophie.t@email.com', '07700 100004', '2024-03-22'),
(5, 'Amara', 'Diallo', 'amara.diallo@email.com', '07700 100005', '2024-04-05'),
(6, 'Tom', 'Griffiths', 'tom.griffiths@email.com', '07700 100006', '2024-05-17'),
(7, 'Leila', 'Nasrin', 'leila.nasrin@email.com', '07700 100007', '2024-06-30'),
(8, 'Ben', 'Kowalski', 'ben.kowalski@email.com', '07700 100008', '2024-08-12'),
(9, 'Nia', 'Williams', 'nia.williams@email.com', '07700 100009', '2024-09-01'),
(10, 'Marcus', 'Bellamy', 'marcus.b@email.com', '07700 100010', '2024-10-19');

-- --------------------------------------------------------

--
-- Table structure for table `orderitem`
--

CREATE TABLE `orderitem` (
  `OrderItemID` int NOT NULL,
  `OrderID` int NOT NULL,
  `ProductID` int NOT NULL,
  `Quantity` int NOT NULL,
  `UnitPrice` decimal(10,2) NOT NULL
) ;

--
-- Dumping data for table `orderitem`
--

INSERT INTO `orderitem` (`OrderItemID`, `OrderID`, `ProductID`, `Quantity`, `UnitPrice`) VALUES
(1, 1, 1, 2, 1.99),
(2, 1, 4, 1, 3.49),
(3, 1, 13, 2, 2.20),
(4, 2, 7, 1, 18.99),
(5, 2, 8, 1, 14.50),
(6, 3, 10, 2, 8.99),
(7, 3, 11, 1, 4.49),
(8, 4, 5, 3, 4.29),
(9, 4, 6, 2, 3.79),
(10, 5, 9, 2, 6.99),
(11, 5, 12, 1, 9.99),
(12, 6, 2, 1, 2.49),
(13, 6, 3, 1, 1.89),
(14, 6, 14, 1, 5.49),
(15, 7, 15, 2, 12.99),
(16, 8, 1, 4, 1.99),
(17, 9, 4, 2, 3.49),
(18, 10, 4, 2, 3.49);

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `OrderID` int NOT NULL,
  `CustomerID` int NOT NULL,
  `OrderDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `OrderStatus` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Pending'
) ;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`OrderID`, `CustomerID`, `OrderDate`, `OrderStatus`) VALUES
(1, 1, '2025-01-15 10:30:00', 'Delivered'),
(2, 2, '2025-02-03 14:00:00', 'Delivered'),
(3, 3, '2025-03-10 09:15:00', 'Shipped'),
(4, 4, '2025-03-22 16:45:00', 'Processing'),
(5, 5, '2025-04-01 11:00:00', 'Pending'),
(6, 1, '2025-04-10 08:30:00', 'Delivered'),
(7, 6, '2025-04-18 13:20:00', 'Shipped'),
(8, 7, '2025-04-25 10:05:00', 'Pending'),
(9, 2, '2026-05-01 03:57:10', 'Pending'),
(10, 2, '2026-05-01 04:23:44', 'Pending');

--
-- Triggers `orders`
--
DELIMITER $$
CREATE TRIGGER `trg_order_insert_audit` AFTER INSERT ON `orders` FOR EACH ROW BEGIN
    INSERT INTO AuditLog (TableName, RecordID, Action, OldValue, NewValue)
    VALUES (
        'Orders',
        NEW.OrderID,
        'INSERT',
        NULL,
        CONCAT('CustomerID=', NEW.CustomerID, ', Status=', NEW.OrderStatus)
    );
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `ProductID` int NOT NULL,
  `ProductName` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Description` text COLLATE utf8mb4_unicode_ci,
  `Price` decimal(10,2) NOT NULL,
  `StockQty` int NOT NULL DEFAULT '0',
  `ExpiryDate` date DEFAULT NULL,
  `SupplierID` int NOT NULL
) ;

--
-- Dumping data for table `product`
--

INSERT INTO `product` (`ProductID`, `ProductName`, `Description`, `Price`, `StockQty`, `ExpiryDate`, `SupplierID`) VALUES
(1, 'Organic Carrots 1kg', 'Freshly harvested British organic carrots, unwashed.', 2.29, 180, '2026-06-01', 1),
(2, 'Organic Broccoli Head', 'Certified organic broccoli head, approx 400g.', 2.69, 140, '2026-05-20', 1),
(3, 'Organic Spinach 250g', 'Baby leaf spinach, washed and ready to eat.', 1.89, 180, '2026-05-15', 1),
(4, 'Organic Apples 6 Pack', 'Cox variety, hand-picked from certified organic orchards.', 3.49, 116, '2026-07-10', 2),
(5, 'Organic Strawberries 400g', 'Sweet seasonal strawberries, no pesticides.', 4.29, 90, '2026-05-25', 2),
(6, 'Organic Blueberries 250g', 'Antioxidant-rich blueberries from certified growers.', 3.79, 110, '2026-05-30', 2),
(7, 'Rose & Jojoba Face Oil 30ml', 'Cold-pressed organic jojoba with rose hip. Suitable for all skin types.', 18.99, 60, NULL, 3),
(8, 'Lavender Body Lotion 200ml', 'Soothing organic lavender in a shea butter base.', 14.50, 75, NULL, 3),
(9, 'Organic Charcoal Soap', 'Activated charcoal bar soap, 100g. Vegan and palm-oil free.', 6.99, 100, NULL, 3),
(10, 'Bamboo Dish Cloths x5', 'Reusable and compostable bamboo cloths. Replaces disposable roll.', 8.99, 85, NULL, 4),
(11, 'Eco Washing Up Liquid 500ml', 'Plant-based, biodegradable formula. Fragrance-free.', 4.49, 130, NULL, 4),
(12, 'Beeswax Wraps x3', 'Reusable food wraps in small, medium, large. Plastic-free alternative.', 9.99, 70, NULL, 4),
(13, 'Organic Whole Milk 1L', 'Unhomogenised organic whole milk from grass-fed cows.', 2.20, 200, '2026-05-18', 5),
(14, 'Organic Cheddar 250g', 'Mature organic cheddar, unpasteurised, made in Somerset.', 5.49, 80, '2026-08-01', 5),
(15, 'Organic Elderberry Syrup', 'Immune support syrup, 150ml. No artificial sweeteners.', 12.99, 55, '2027-01-01', 5),
(16, 'Organic Peppermint Tea 20 bags', 'Refreshing organic peppermint tea, individually wrapped bags.', 3.29, 80, '2027-06-01', 1),
(18, 'Organic Peppermint Tea 20 bags', 'Refreshing organic peppermint tea.', 3.29, 80, '2027-06-01', 1);

--
-- Triggers `product`
--
DELIMITER $$
CREATE TRIGGER `trg_product_price_audit` AFTER UPDATE ON `product` FOR EACH ROW BEGIN
    IF OLD.Price <> NEW.Price THEN
        INSERT INTO AuditLog (TableName, RecordID, Action, OldValue, NewValue)
        VALUES (
            'Product',
            NEW.ProductID,
            'PRICE_UPDATE',
            CONCAT('Price=', OLD.Price),
            CONCAT('Price=', NEW.Price)
        );
    END IF;
END
$$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `trg_product_stock_audit` AFTER UPDATE ON `product` FOR EACH ROW BEGIN
    IF OLD.StockQty <> NEW.StockQty THEN
        INSERT INTO AuditLog (TableName, RecordID, Action, OldValue, NewValue)
        VALUES (
            'Product',
            NEW.ProductID,
            'STOCK_UPDATE',
            CONCAT('StockQty=', OLD.StockQty),
            CONCAT('StockQty=', NEW.StockQty)
        );
    END IF;
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `productcategory`
--

CREATE TABLE `productcategory` (
  `ProductID` int NOT NULL,
  `CategoryID` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `productcategory`
--

INSERT INTO `productcategory` (`ProductID`, `CategoryID`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 2),
(6, 2),
(15, 2),
(7, 3),
(8, 3),
(9, 3),
(10, 4),
(11, 4),
(12, 4),
(13, 5),
(14, 5),
(15, 6);

-- --------------------------------------------------------

--
-- Table structure for table `review`
--

CREATE TABLE `review` (
  `ReviewID` int NOT NULL,
  `CustomerID` int NOT NULL,
  `ProductID` int NOT NULL,
  `Rating` tinyint NOT NULL,
  `ReviewText` text COLLATE utf8mb4_unicode_ci,
  `ReviewDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ;

--
-- Dumping data for table `review`
--

INSERT INTO `review` (`ReviewID`, `CustomerID`, `ProductID`, `Rating`, `ReviewText`, `ReviewDate`) VALUES
(1, 1, 1, 5, 'Really fresh carrots, much better than supermarket ones.', '2025-01-20 12:00:00'),
(2, 2, 7, 5, 'Absolutely love this face oil. My skin feels incredible.', '2025-02-10 09:30:00'),
(3, 3, 10, 4, 'Good quality cloths, very absorbent. Delivery was quick.', '2025-03-15 14:00:00'),
(4, 4, 5, 5, 'Best strawberries I have ever tasted, so sweet.', '2025-03-25 11:00:00'),
(5, 5, 9, 3, 'Nice soap but quite small for the price.', '2025-04-05 16:30:00'),
(6, 6, 15, 4, 'Seems to be helping with winter colds. Will buy again.', '2025-04-20 10:00:00'),
(7, 1, 13, 5, 'The milk is amazing, proper creamy. Tastes like it should.', '2025-04-12 08:00:00'),
(8, 7, 12, 4, 'Great alternative to cling film, keeps food fresh well.', '2025-04-27 15:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `supplier`
--

CREATE TABLE `supplier` (
  `SupplierID` int NOT NULL,
  `SupplierName` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ContactEmail` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Phone` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `CertificationInfo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `RegistrationDate` date NOT NULL DEFAULT (curdate())
) ;

--
-- Dumping data for table `supplier`
--

INSERT INTO `supplier` (`SupplierID`, `SupplierName`, `ContactEmail`, `Phone`, `CertificationInfo`, `RegistrationDate`) VALUES
(1, 'Green Valley Farms', 'contact@greenvalley.co.uk', '01234 567890', 'Soil Association Certified, OF&G Member', '2022-03-15'),
(2, 'Pure Earth Organics', 'hello@pureearth.co.uk', '01234 678901', 'Demeter Biodynamic Certified', '2022-06-01'),
(3, 'EcoSkin Ltd', 'info@ecoskin.co.uk', '01234 789012', 'Cosmos Organic Certified, Leaping Bunny Approved', '2023-01-10'),
(4, 'Natural Home Co', 'sales@naturalhome.co.uk', '01234 890123', 'BSI Eco Verified', '2023-04-20'),
(5, 'Harvest Moon Organics', 'orders@harvestmoon.co.uk', '01234 901234', 'Soil Association Certified', '2023-09-05');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `UserID` int NOT NULL,
  `Username` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `PasswordHash` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Role` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'admin',
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`UserID`, `Username`, `PasswordHash`, `Role`, `CreatedAt`) VALUES
(1, 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', '2026-05-01 03:57:10');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `auditlog`
--
ALTER TABLE `auditlog`
  ADD PRIMARY KEY (`LogID`);

--
-- Indexes for table `category`
--
ALTER TABLE `category`
  ADD PRIMARY KEY (`CategoryID`),
  ADD UNIQUE KEY `uq_category_name` (`CategoryName`);

--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
  ADD PRIMARY KEY (`CustomerID`),
  ADD UNIQUE KEY `uq_customer_email` (`Email`);

--
-- Indexes for table `orderitem`
--
ALTER TABLE `orderitem`
  ADD PRIMARY KEY (`OrderItemID`),
  ADD KEY `idx_orderitem_order` (`OrderID`),
  ADD KEY `idx_orderitem_product` (`ProductID`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`OrderID`),
  ADD KEY `idx_order_customer` (`CustomerID`);

--
-- Indexes for table `product`
--
ALTER TABLE `product`
  ADD PRIMARY KEY (`ProductID`),
  ADD KEY `idx_product_supplier` (`SupplierID`),
  ADD KEY `idx_product_name` (`ProductName`);

--
-- Indexes for table `productcategory`
--
ALTER TABLE `productcategory`
  ADD PRIMARY KEY (`ProductID`,`CategoryID`),
  ADD KEY `fk_pc_category` (`CategoryID`);

--
-- Indexes for table `review`
--
ALTER TABLE `review`
  ADD PRIMARY KEY (`ReviewID`),
  ADD KEY `fk_rev_customer` (`CustomerID`),
  ADD KEY `idx_review_product` (`ProductID`);

--
-- Indexes for table `supplier`
--
ALTER TABLE `supplier`
  ADD PRIMARY KEY (`SupplierID`),
  ADD UNIQUE KEY `uq_supplier_email` (`ContactEmail`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`UserID`),
  ADD UNIQUE KEY `uq_username` (`Username`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `auditlog`
--
ALTER TABLE `auditlog`
  MODIFY `LogID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `category`
--
ALTER TABLE `category`
  MODIFY `CategoryID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `customer`
--
ALTER TABLE `customer`
  MODIFY `CustomerID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `orderitem`
--
ALTER TABLE `orderitem`
  MODIFY `OrderItemID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `OrderID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
  MODIFY `ProductID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `review`
--
ALTER TABLE `review`
  MODIFY `ReviewID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier`
--
ALTER TABLE `supplier`
  MODIFY `SupplierID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `UserID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `orderitem`
--
ALTER TABLE `orderitem`
  ADD CONSTRAINT `fk_oi_order` FOREIGN KEY (`OrderID`) REFERENCES `orders` (`OrderID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_oi_product` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `fk_order_customer` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Constraints for table `product`
--
ALTER TABLE `product`
  ADD CONSTRAINT `fk_product_supplier` FOREIGN KEY (`SupplierID`) REFERENCES `supplier` (`SupplierID`) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Constraints for table `productcategory`
--
ALTER TABLE `productcategory`
  ADD CONSTRAINT `fk_pc_category` FOREIGN KEY (`CategoryID`) REFERENCES `category` (`CategoryID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_pc_product` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `review`
--
ALTER TABLE `review`
  ADD CONSTRAINT `fk_rev_customer` FOREIGN KEY (`CustomerID`) REFERENCES `customer` (`CustomerID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_rev_product` FOREIGN KEY (`ProductID`) REFERENCES `product` (`ProductID`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
